from typing import List
from flask import current_app

from app import cache, db
from app.models import Category, Article, CommonPage, Tag
from app.common.enums import PublishType

log = current_app.logger


@cache.memoize()
def category_list() -> List[Category]:
    """获取分类列表
    """
    log.info('获取分类列表')
    return Category.query.all()


@cache.memoize()
def tag_list() -> List[Tag]:
    """文章标签列表
    """
    log.info('获取文章标签列表')
    return Tag.query.order_by(Tag.id.desc()).all()


@cache.memoize()
def article_date_group(category_id: int | None = None) -> List[str]:
    """文章发布日期列表
    category_id: 分类id
    """
    log.info('获取文章发布日期列表')
    sql = "SELECT DISTINCT(date_format(create_at,'%Y-%m')) AS date FROM article"
    if category_id is not None:
        sql += ' WHERE category_id = ' + str(category_id)
    data = db.session.execute(sql).fetchall()
    return [x[0] for x in data]


@cache.memoize()
def article_page(
        category_id: int,
        title: str | None = None,
        date: str | None = None,
        current=1,
        page_size=10
) -> CommonPage:
    """获取文章分页
    category_id: 分类ID
    title: 标题
    date: 年月
    current: 当前页
    page_size: 每页大小
    """
    log.info('获取文章分页')
    # 分类+发布
    query = Article.query.filter(
        Article.category_id == category_id,
        Article.has_type == PublishType.show
    ).order_by(Article.id.desc())
    # 传入title模糊搜索
    if title and title.strip():
        query = query.filter(Article.title.like('%{keyword}%'.format(keyword=title)))
    # 筛选日期
    if date and date.strip():
        query = query.filter(db.func.date_format(Article.create_at, '%Y-%m') == date)
    # 分页查询
    page_data = query.paginate(
        page=current,
        per_page=page_size,
        error_out=False
    )
    return CommonPage(page_data)


@cache.memoize()
def article_tag(tag_id: int) -> List[Article]:
    """文章标签列表
    """
    log.info('获取文章标签列表')
    # 查询出全部关联的文章
    sql = "SELECT article_id FROM article_tag WHERE tag_id=%s" % tag_id
    data = db.session.execute(sql).fetchall()
    # 提取文章的id
    article_ids = [x[0] for x in data]
    # 筛选出对应的文章
    return Article.query.filter(Article.id.in_(article_ids)).all()


@cache.memoize()
def article_detail(article_id: int) -> Article:
    """查询文章详情
    article_id: 文章ID
    """
    log.info('查询文章详情')
    return Article.query.get(article_id)
